"""
Case Type   : SQL
Case Name   : 创建表空间限制maxsize，创建数据库，创建表后插入超出表空间大小的数据失败，truncate表后插入少量数据
Create At   : 2024/10/14
Owner       : nanyang12
Description :
        1.创建表空间和数据库，创建成功
        2.切换到步骤1创建的数据库，创建表并插入大量数据
        3.truncate 表后插入少量数据
        4.清理环境
Expect      :
        1.创建成功
        2.表创建成功，数据插入失败
        3.执行失败
        4.清理环境成功
History     :
"""
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

LOG = Logger()
commonsh = CommonSH('dbuser')


class DeveloperOptions(unittest.TestCase):
    def setUp(self):
        self.constant = Constant()
        self.user_node = Node('dbuser')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        LOG.info(
            '------Opengauss_Function_DDL_Tablespace_Case0042start----')

    def test_ddl_table(self):
        step1 = '--step1:创建表空间和数据库，创建成功--'
        LOG.info(step1)
        sql_cmd = '''drop database if exists dbsegment; 
                     drop tablespace if exists tabsp;
                     create tablespace tabsp relative location 'tabsp' maxsize '15M'; 
                     create database dbsegment tablespace tabsp; 
                     select * from pg_database_size('dbsegment'); 
                    '''
        excute_cmd1 = f'''source {self.DB_ENV_PATH};
            gsql -d postgres -p {self.user_node.db_port} -c "{sql_cmd}"
            '''
        LOG.info(excute_cmd1)
        msg1 = self.user_node.sh(excute_cmd1).result()
        LOG.info(msg1)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1)

        step2 = '--step2:切换到步骤1创建的数据库，创建表并插入大量数据，表创建成功，数据插入失败--'
        LOG.info(step2)
        sql_cmd = '''create table test(id int, name text);
                     insert into test values(generate_series(1,500000000), generate_series(1,500000000)); 
                  '''
        excute_cmd1 = f'''source {self.DB_ENV_PATH};
            gsql -d dbsegment -p {self.user_node.db_port} -c "{sql_cmd}"
            '''
        LOG.info(excute_cmd1)
        msg1 = self.user_node.sh(excute_cmd1).result()
        LOG.info(msg1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg1)
        self.assertIn("ERROR:  Insufficient storage space for tablespace", msg1, "执行失败" + step2)

        step3 = '--step3:truncate 表后插入少量数据;expect:成功---'
        LOG.info(step3)
        sql_cmd = '''
                     alter tablespace tabsp resize maxsize '50M';
                     truncate table test; 
                     insert into test values(generate_series(1,500), generate_series(1,500));
                  '''
        excute_cmd1 = f'''source {self.DB_ENV_PATH};
            gsql -d dbsegment -p {self.user_node.db_port} -c "{sql_cmd}"
            '''
        LOG.info(excute_cmd1)
        msg1 = self.user_node.sh(excute_cmd1).result()
        LOG.info(msg1)
        self.assertNotIn("ERROR", msg1, "执行失败" + step3)
        self.assertIn(self.constant.TRUNCATE_SUCCESS_MSG, msg1)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, msg1)

    def tearDown(self):
        step4 = '--step4:清理环境--'
        LOG.info(step4)
        sql_cmd = '''drop database if exists dbsegment; 
                     drop tablespace if exists  tabsp;
                    '''
        excute_cmd1 = f'''source {self.DB_ENV_PATH};
                    gsql -d postgres -p {self.user_node.db_port} -c "{sql_cmd}"
                    '''
        LOG.info(excute_cmd1)
        msg1 = self.user_node.sh(excute_cmd1).result()
        LOG.info(msg1)
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg1)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg1)
        LOG.info(
            '------Opengauss_Function_DDL_Tablespace_Case0042finish----')
